package cn.whit.blog.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.whit.blog.dao.UserDao;
import cn.whit.blog.domain.User;
import cn.whit.blog.util.DynamicQuery;
import cn.whit.blog.util.JDBCUtils;
import cn.whit.blog.util.Parameter;

public class UserDaoImpl implements UserDao{

	@Override
	public User findById(String id) throws SQLException {
		String sql="select * from user where id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<User>(User.class),id);
	}

	@Override
	public List<User> findAll() throws SQLException {
		String sql="select * from user";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<User>(User.class));
	}
	
	@Override
	public int findTotalRecords() throws Exception {
		String sql="select count(*) from user";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}
	
	@Override
	public List<User> findAllWithPage(int startIndex, int pageSize) throws Exception {
		String sql="select * from user order by id desc limit  ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<User>(User.class),startIndex,pageSize);
		
	}
	
	@Override
	public int findTotalRecordsAndQuery(DynamicQuery query) throws Exception {
		String sql="select count(*) from user where 1=1 ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size()];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		Long num=(Long)qr.query(sql, new ScalarHandler(),objs);
		return num.intValue();
	}
	
	@Override
	public List<User> findAllWithPageAndQuery(int startIndex, int pageSize,DynamicQuery query) throws Exception {
		String sql="select * from user where 1=1 ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		sql += " order by id desc limit  ? , ? ";
		
        //如果想排序，自行在sql后添加   
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size() + 2];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		objs[i] = startIndex;
		objs[i+1] = pageSize;
		
		return qr.query(sql, new BeanListHandler<User>(User.class),objs);
	}

	@Override
	public List<User> selectByQuery(DynamicQuery query) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public  List<User> findByUserNameOrEmail(String userName, String email) throws SQLException {
		String sql="select * from user where name = ? or email = ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<User>(User.class),userName,email);
	}

	@Override
	public void save(User newUser) throws SQLException {
		String sql="INSERT INTO user(name,email,password,regDateTime,status,activecode ) "
				+ " VALUES(?,?,?,?,?,?)";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={newUser.getName(),newUser.getEmail(),newUser.getPassword(),
				newUser.getRegDateTime(),newUser.getStatus(),newUser.getActivecode()};
		qr.update(sql,params);
	}

	@Override
	public List<User> findByUserNameAndPassword(String userName, String password) throws SQLException {
		String sql="select * from user where name = ? and password = ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<User>(User.class),userName,password);
	}

	@Override
	public List<User> findByActivecode(String activecode) throws SQLException {
		String sql="select * from user where activecode = ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<User>(User.class),activecode);
	}

	@Override
	public void update(User user) throws SQLException {
		String sql="update user set name= ? ,icon=? ,status =? ,memo =? ,regDateTime =? , birthday =?  ,email =? ,phone= ? , password = ? , groupid = ?, activecode = ? WHERE id=?";
		Object[] params={user.getName(),user.getIcon(),user.getStatus(),user.getMemo(),user.getRegDateTime(),
						user.getBirthday(),user.getEmail(),user.getPhone(),user.getPassword(),user.getGroupid(),user.getActivecode(),user.getId()};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
	}

	@Override
	public  List<User> findByEmail(String email) throws SQLException {
		String sql="select * from user where email = ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<User>(User.class),email);
	}

	@Override
	public void deleteById(String s) throws SQLException {
		String sql=" delete from user where id = ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={s};
		qr.update(sql,params);
	}
	
}
